﻿//DevNETCoder
//CopyRight By DevNET Group
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using STT.Entities;
using STT.DataAccess;
 
namespace STT.Business
{
    public class BTbTKB
    {
        //-----------------------------------------------------------//
        public static bool KiemTraDuLieu(ETbTKB TKB)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter(@"MaMonHoc", TKB.MaMonHoc);
            pr[1] = new SqlParameter(@"MaGV", TKB.MaGV);
            pr[2] = new SqlParameter(@"NgayHoc", TKB.NgayHoc);
            pr[3] = new SqlParameter(@"CaHoc", TKB.CaHoc);
            pr[4] = new SqlParameter(@"PhongHoc", TKB.PhongHoc);
            pr[5] = new SqlParameter(@"MaLop", TKB.MaLop);
         


            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_KiemTraDuLieu", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        public static void DeleteByMaGV(string MaGV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaGV", MaGV);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbTKB_DeleteByMaGV", pr);
        }
        
        //-----------------------------------------------------------//
        public static bool KiemTraDuLieu_Sua(ETbTKB TKB)
        {
            SqlParameter[] pr = new SqlParameter[7];
            pr[0] = new SqlParameter(@"MaMonHoc", TKB.MaMonHoc);
            pr[1] = new SqlParameter(@"MaGV", TKB.MaGV);
            pr[2] = new SqlParameter(@"NgayHoc", TKB.NgayHoc);
            pr[3] = new SqlParameter(@"CaHoc", TKB.CaHoc);
            pr[4] = new SqlParameter(@"PhongHoc", TKB.PhongHoc);
            pr[5] = new SqlParameter(@"MaLop", TKB.MaLop);
            pr[6] = new SqlParameter(@"MaTKB", TKB.MaTKB);
          

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_KiemTraDuLieu_Sua", pr);
            if (all.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        //----------------------------------------------------//
        public static DataTable TbTKB_SelectByMaGVMaMHMaLop(string MaGV, string Malop, string MaMH)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter(@"MaGV", MaGV);
            pr[1] = new SqlParameter(@"MaMH", int.Parse(MaMH));
            pr[2] = new SqlParameter(@"MaLop", int.Parse(Malop));
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectByMaGVMaMHMaLop", pr);
            return all;
        }
        public static DataTable TbTKB_SelectByTKBMaMHMaLop(string Malop, string MaMH)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaMH", int.Parse(MaMH));
            pr[1] = new SqlParameter(@"MaLop", int.Parse(Malop));
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectByTKBMaMHMaLop", pr);
            return all;
        }
        public static DataTable TbTKB_SelectByMaGVMaMHMaLopExport(string MaGV, string Malop, string MaMH)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter(@"MaGV", MaGV);
            pr[1] = new SqlParameter(@"MaMH", int.Parse(MaMH));
            pr[2] = new SqlParameter(@"MaLop", int.Parse(Malop));
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectByMaGVMaMHMaLopExport", pr);
            return all;
        }
        public static DataTable TbTKB_SelectByMaMHMaLop(string Malop, string MaMH)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaMH", int.Parse(MaMH));
            pr[1] = new SqlParameter(@"MaLop", int.Parse(Malop));
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectByMaMHMaLop", pr);
            return all;
        }
        /// </summary>
        /// <param name="MaLop"></param>
        /// <returns></returns>
        public static DataTable TooltipTKB(string MaLop)
        {

            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Mahv", MaLop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TooltipTKB", pr);
            return all;
        }
        //-----------------------------------------------------------//
        public static DataTable TbTKB_Search(String Tengv)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"TenGV", Tengv);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_Search", pr);
            return all;
        }
        //-----------------------------------------------------------//
        public static DataTable TbTKB_SecectAllView_Xuat(String nguoitao,int maLop)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"NguoiTao", nguoitao);
            pr[1] = new SqlParameter(@"MaLop", maLop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectAll_View_Xuat", pr);
            return all;
        }
        //-----------------------------------------------------------//
        public static DataTable TbTKB_SelectAll_UD()
        {

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectAll_View_UD", null);
            return all;
        }
        //-----------------------------------------------------------//
        public static DataTable TbTKB_SelectAll_PhongHoc()
        {

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbPhongHoc_SelectAll", null);
            return all;
        }
        //-----------------------------------------------------------//
        public static DataTable TbTKB_SelectAll_CaHoc()
        {

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbCaHoc_SelectAll", null);
            return all;
        }
      
        public static DataTable SelectAll_Xuat_Lop(int lop)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"maLop", lop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectAll_Xuat_Lop", pr);
            return all;
        }
        //-----------------------------------------------------------//
        public DataTable SelectAll(string mahv)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Mahv", mahv);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectAll", pr);
            return all;
        }


        public static DataTable SelectAllByIdClass(string malop)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaLop", malop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectAllByMaLop", pr);
            return all;
        }

        public static DataTable SelectAllByIdClassNot(string malop)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaLop", malop);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectAll_View", pr);
            return all;
        }

        public static DataTable SelectNoCaHoc(string ngay)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"NgayHoc", ngay);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectNoCaHoc", pr);
            return all;
        }

        public static DataTable SelectNoGV(string ngay)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"NgayHoc", ngay);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectNoGV", pr);
            return all;
        }

        public static DataTable SelectNoMonHoc(string ngay)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"NgayHoc", ngay);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectNoMonHoc", pr);
            return all;
        }

        public static DataTable SelectNoPH(string ngay)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"NgayHoc", ngay);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectNoPH", pr);
            return all;
        }

        public static DataTable SelectAll_View()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectAll_View", null);
            return all;
        }
      
        public static DataTable SelectTop(int Top)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Top", Top);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_SelectTop", pr);
            return all;
        }
        //-----------------------------------------------------------//
        public static DataTable SearchTenMonHoc(String MaMH)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaMH", MaMH);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbTKB_Search", pr);
            return all;
        }
        //-----------------------------------------------------------//
        public static void Insert(ETbTKB OTbTKB)
        {
            SqlParameter[] pr = new SqlParameter[7];
            pr[0] = new SqlParameter(@"MaMonHoc", OTbTKB.MaMonHoc);
            pr[1] = new SqlParameter(@"MaGV", OTbTKB.MaGV);
            pr[2] = new SqlParameter(@"NgayHoc", OTbTKB.NgayHoc);
            pr[3] = new SqlParameter(@"CaHoc", OTbTKB.CaHoc);
            pr[4] = new SqlParameter(@"PhongHoc", OTbTKB.PhongHoc);
            pr[5] = new SqlParameter(@"MaLop", OTbTKB.MaLop);
            pr[6] = new SqlParameter(@"DiemDanh", OTbTKB.DiemDanh);
  
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbTKB_Insert", pr);
        }
        //-----------------------------------------------------------//
        public static void Update(ETbTKB OTbTKB)
        {
            SqlParameter[] pr = new SqlParameter[8];
            pr[0] = new SqlParameter(@"MaTKB", OTbTKB.MaTKB);
            pr[1] = new SqlParameter(@"MaMonHoc", OTbTKB.MaMonHoc);
            pr[2] = new SqlParameter(@"MaGV", OTbTKB.MaGV);
            pr[3] = new SqlParameter(@"NgayHoc", OTbTKB.NgayHoc);
            pr[4] = new SqlParameter(@"CaHoc", OTbTKB.CaHoc);
            pr[5] = new SqlParameter(@"PhongHoc", OTbTKB.PhongHoc);
            pr[6] = new SqlParameter(@"MaLop", OTbTKB.MaLop);
            pr[7] = new SqlParameter(@"DiemDanh", OTbTKB.DiemDanh);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbTKB_Update", pr);
        }
         //-----------------------------------------------------------//
        public static void Update_State(ETbTKB OTbTKB)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaTKB", OTbTKB.MaTKB);
            pr[1] = new SqlParameter(@"DiemDanh", OTbTKB.DiemDanh);

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbTKB_Update_State", pr);
        }
        //-----------------------------------------------------------//
        public static void Update_State_all(int T)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"DiemDanh", T);

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbTKB_Update_State_ALL", pr);
        }
    
        //-----------------------------------------------------------//
        public static void Delete(int MaTKB)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaTKB", MaTKB);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbTKB_Delete", pr);
        }
        //-----------------------------------------------------------//
        public static void DeleteAll(int TrangThai)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"TrangThai", TrangThai);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbTKB_Delete_All", pr);
        }
        //-----------------------------------------------------------//
        public static ETbTKB SelectByID(int MaTKB)
        {
            ETbTKB OTbTKB = new ETbTKB();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaTKB", MaTKB);
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbTKB_SelectByID", pr);
            if (idr.Read())
                OTbTKB = GetOneTbTKB(idr);
            idr.Close();
            idr.Dispose();
            return OTbTKB;
        }
        //-----------------------------------------------------------//
        private static ETbTKB GetOneTbTKB(IDataReader idr)
        {
            ETbTKB OTbTKB = new ETbTKB();
            if (idr["MaTKB"] != DBNull.Value)
                OTbTKB.MaTKB = (int)idr["MaTKB"];
          
            if (idr["MaMonHoc"] != DBNull.Value)
                OTbTKB.MaMonHoc = (int)idr["MaMonHoc"];
            if (idr["MaGV"] != DBNull.Value)
                OTbTKB.MaGV = (string)idr["MaGV"];
            if (idr["NgayHoc"] != DBNull.Value)
                OTbTKB.NgayHoc = (DateTime)idr["NgayHoc"];
           
    
            if (idr["MaLop"] != DBNull.Value)
                OTbTKB.MaLop = (int)idr["MaLop"];
            return OTbTKB;
        }
        //-----------------------------------------------------------//
        public static bool TestByID(int MaTKB)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaTKB", MaTKB);
            pr[1] = new SqlParameter(@"TestID", SqlDbType.Bit);
            pr[1].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbTKB_TestByID", pr);
            return Convert.ToBoolean(pr[1].Value);
        }
        //-----------------------------------------------------------//
        public static List<ETbTKB> ListAll()
        {
            List<ETbTKB> list = new List<ETbTKB>();
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbTKB_SelectAll", null);
            while (idr.Read())
                list.Add(GetOneTbTKB(idr));
            if (idr.IsClosed == false)
            {
                idr.Close();
                idr.Dispose();
            }
            return list;
        }

        public static List<ETbTKB> ListTop(int Top)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Top", Top);
            List<ETbTKB> list = new List<ETbTKB>();
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbTKB_SelectAll", pr);
            while (idr.Read())
                list.Add(GetOneTbTKB(idr));
            if (idr.IsClosed == false)
            {
                idr.Close();
                idr.Dispose();
            }
            return list;
        }
    }
 
}